﻿// Finaquant Analytics - http://finaquant.com/
// Copyright © Finaquant Analytics GmbH
// Email: support@finaquant.com

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using FinaquantCalcs;

// Excel DNA: Integrate .NET into Excel
// http://exceldna.codeplex.com/
using ExcelDna.Integration;
using ExcelDna.ComInterop;
using CustomUI = ExcelDna.Integration.CustomUI;
// using ExcelDna.Integration.ExcelDnaUtil  // not needed for Application now we have NetOffice 

// NetOffice: Microsoft Office integration without version limitations
// see: http://netoffice.codeplex.com/
using Excel = NetOffice.ExcelApi;
using NetOffice.ExcelApi.GlobalHelperModules;
using NetOffice.ExcelApi.Enums;
using XTable = NetOffice.ExcelApi.ListObject;

namespace FinaquantInExcel
{
    /// <summary>
    /// Class for ad-hoc tests in Console Application mode
    /// </summary>
    public class Test
    {
        // Some arbitrary excel tests: 
        // Get Application instance, open workbook, check if workbook is open..
        internal static void Test_ExcelObjects()
        {
            // Programatically get path of open Excel/Word-document
            // http://stackoverflow.com/questions/8963529/programatically-get-path-of-open-excel-word-document

            //Gets Excel and gets Activeworkbook and worksheet
            // http://stackoverflow.com/questions/7916711/get-the-current-workbook-object-in-c-sharp

            // declare parameters
            Excel.Application xlapp;
            Excel.Workbook wb;
            Excel.Worksheet ws;

            // get an active excel application instance; start one if necessary
            // xlapp = new Excel.Application(null, ExcelDnaUtil.Application);

            // NetOffice.ExcelApi.Application.GetActiveInstance return null
            // see: https://netoffice.codeplex.com/workitem/20470

            xlapp = ExcelFunc_NO.GetExcelApplicationInstance();
            xlapp.Visible = true;

            // demo excel file in Resources directory
            string filename;
            // filename = "FinaquantExcelDemo_2010.xlsm";
            filename = "FinaquantExcelDemo_2003.xls";

            string ProjectPath = Path.GetDirectoryName(Path.GetDirectoryName(System.IO.Directory.GetCurrentDirectory()));
            string filepath = ProjectPath + @"\Resources\" + filename;

            System.Diagnostics.Debug.WriteLine("Excel file path: " + filepath);

            // check if excel workbook is open
            bool ifOpen = ExcelFunc_NO.IsExcelWBOpen1(xlapp, filepath);
            Console.WriteLine("If WB open: {0}", ifOpen);

            // open excel file in editable mode
            if (!ExcelFunc_NO.IsExcelWBOpen2(xlapp, filepath))
                wb = (Excel.Workbook)xlapp.Workbooks.Open(filepath, Type.Missing, false);
            else
                // wb = xlapp.Workbooks.get_Item(filename);
                wb = xlapp.Workbooks[filename];

            // Get full name of workbook (file path)
            Console.WriteLine("Workbook: {0}", wb.FullName);

            // Get worksheet, select a range
            // ws = wb.Worksheets.get_Item(1);
            ws = (Excel.Worksheet)wb.Worksheets[1];
            ws.Activate();

            // offset
            Excel.Range rng = ws.Range("A1:B3").Offset(0, 4);

            // resize
            rng = rng.Resize(4, 4);

            // assign a single value to range
            // rng.Value2 = 5;

            // ws.Range("A1:B3").Select();
            rng.Select();
            ws.Visible = XlSheetVisibility.xlSheetVisible;

            // check if excel WB is opened
            Console.WriteLine("If WB open (method 1): {0}", ExcelFunc_NO.IsExcelWBOpen1(xlapp, filename));
            Console.WriteLine("If WB open (method 2): {0}", ExcelFunc_NO.IsExcelWBOpen2(xlapp, filepath));

            Console.WriteLine("Active workbook: " + xlapp.ActiveWorkbook.Name);
            Console.WriteLine("# open workbooks: " + xlapp.Workbooks.Count);
            Console.WriteLine("Active worksheet: " + ((Excel.Worksheet)xlapp.ActiveWorkbook.ActiveSheet).Name);


            // list all opened workbooks
            Console.WriteLine("List of all opened workbooks:");

            foreach (var wbook in xlapp.Workbooks)
            {
                Console.WriteLine("Excel file: " + ((Excel.Workbook)wbook).FullName);
            }

            // generate a new Guid
            Console.WriteLine("GUID: " + Guid.NewGuid());

            // find all ListObjects in workbook
            Console.WriteLine("List of all ListObjects in workbook " + filename);
            string[] TableNames;
            Excel.ListObject[] xlist = ExcelFunc_NO.GetAllExcelTables(wb, out TableNames);
            Console.WriteLine(String.Join(" , ", TableNames));

            // test object type
            string s = "ali";
            object obj = s;
            System.Diagnostics.Debug.WriteLine("object type = " + obj.GetType());

            // test cell values & types
            string field, ft, tp, tblname = "Cost";
            object val, val2;
            Excel.ListObject tbl = ExcelFunc_NO.GetListObject(wb, tblname);

            Console.WriteLine("Cell values and types of " + tblname);

            for (int i = 1; i <= tbl.ListColumns.Count; i++)
            {
                field = tbl.ListColumns[i].Name;
                val = tbl.Range[2, i].Value;
                val2 = tbl.Range[2, i].Value2;
                ft = tbl.Range[2, i].NumberFormat.ToString();
                tp = tbl.Range[2, i].GetType().FullName;

                Console.WriteLine("********** field: " + field);
                Console.WriteLine("Value type: " + val.GetType().Name);
                Console.WriteLine("Value2 type: " + val2.GetType().Name);
                Console.WriteLine("Number format: " + ft);
                // Console.WriteLine("Full type name: " + tp);
                Console.WriteLine("Field type: "
                    + ExcelFunc_NO.GetFieldTypeOfExcelCell(tbl.Range[2, i]).ToString());
            }

            Console.WriteLine("typeof(string).Name: " + typeof(string).Name);
            Console.WriteLine("typeof(double).Name: " + typeof(double).Name);

            Console.WriteLine("Press a key to quit");
            Console.ReadKey();
        }

        /// <summary>
        /// Test Read/Write MatrixTableX from/to an excel table (ListObject)
        /// </summary>
        internal static void Test_ReadWrite_ExcelTable()
        {
            
            // demo excel file in Resources directory
            string ProjectPath = Path.GetDirectoryName(Path.GetDirectoryName(System.IO.Directory.GetCurrentDirectory()));
            
            // excel file for tests
            string xfilename;
            // xfilename = "FinaquantExcelDemo_2003.xls";
            xfilename = "FinaquantExcelDemo_2010.xlsm";

            string xfilepath = ProjectPath + @"\Resources\" + xfilename;
            // string xfilepath = @"C:\Users\...\CalcsExcelIntegration\CalcsExcelIntegration1.01\Resources\CalcsExcelDemo.xlsm";

            System.Diagnostics.Debug.WriteLine("Filepath of " + xfilename + ": " + xfilepath);

            // define metadata
            MetaData md = MetaData.CreateEmptyMetaData();
            MetaData.AddNewField(md, "country", FieldType.TextAttribute);
            MetaData.AddNewField(md, "category", FieldType.TextAttribute);
            MetaData.AddNewField(md, "brand", FieldType.TextAttribute);
            MetaData.AddNewField(md, "product", FieldType.TextAttribute);
            MetaData.AddNewField(md, "modelyear", FieldType.IntegerAttribute);
            MetaData.AddNewField(md, "typeid", FieldType.IntegerAttribute);
            MetaData.AddNewField(md, "date", FieldType.DateAttribute);
            MetaData.AddNewField(md, "costs", FieldType.KeyFigure);
            MetaData.AddNewField(md, "price", FieldType.KeyFigure);
            MetaData.AddNewField(md, "margin", FieldType.KeyFigure);
            MetaData.AddNewField(md, "sales", FieldType.KeyFigure);

            // create tables
            MatrixTable CostTable, MarginTable1, MarginTable2;
            Create_TestTables(md, out CostTable, out MarginTable1, out MarginTable2);
            CostTable = CostTable.SelectRows(NumVector.CreateSequenceVector(0,1,100));

            MatrixTableX xtbl = new MatrixTableX(CostTable);

            // get current application instance
            // excel file CalcsExcelDemo must be opened
            Excel.Application xlapp = ExcelFunc_NO.GetExcelApplicationInstance();
            Excel.Workbook wb;

            // check if workbook is open
            if (!ExcelFunc_NO.IsExcelWBOpen2(xlapp, xfilepath))
            {
                System.Diagnostics.Debug.WriteLine("Workbook was not already opened, now open it.");
                return;
            }
            else
            {
                wb = ExcelFunc_NO.GetWorkbookByFullName(xlapp, xfilepath);
            }
            // get workbook object, open workbook if closed
            // Excel.Workbook wb = HelperFunc.OpenExcelWorkbook(xlapp, xfilepath, ReadOnly: false);

            // make workbook visible
            xlapp.Visible = true;
            wb.Activate();

            // get workbook object
            // Excel.Workbook wb = xlapp.Workbooks.get_Item(xfilename);  // filepath causes errors
            // Excel.Workbook wb = xlapp.Workbooks[xfilename];

            System.Diagnostics.Debug.WriteLine("Name of 1. sheet: " + ((Excel.Worksheet)wb.Worksheets[1]).Name);

            // get worksheet object
            System.Diagnostics.Debug.WriteLine("Get worksheet object, named Sheet1");
            
            // Excel.Worksheet wsheet = wb.Worksheets.get_Item("Sheet1");
            // Excel.Worksheet wsheet = (Excel.Worksheet) wb.Worksheets["Sheet1"];
            Excel.Worksheet wsheet = ExcelFunc_NO.GetWorksheet(wb, "Sheet1",
                AddSheetIfNotFound: true);

            System.Diagnostics.Debug.WriteLine("Worksheet name: " + wsheet.Name);

            // Write MatrixTableX to a excel table named "XCost", at cell address "C3"
            System.Diagnostics.Debug.WriteLine("Write MatrixTableX to an Excel Table");
            string TableName = "XCost";
            Excel.ListObject xlist = xtbl.WriteToExcelTable(wsheet, TableName, "C3:F8", ClearSheetContent: true);
            wsheet.Activate();

            // Excel.ListObject xlist = HelperFunc.GetListObject(wb, "XCost");

            // Read MatrixTableX from excel table (ListObject)
            System.Diagnostics.Debug.WriteLine("Read MatrixTableX from an Excel Table");
            MatrixTableX xtbl2 = new MatrixTableX();
            xtbl2.ReadFromExcelTable(xlist, md);
            xtbl2.ViewTable("MatrixTableX read from excel table");

            // view fields and types in MetaData
            MetaDataX mdx = new MetaDataX(md);
            MatrixTable.View_MatrixTable(mdx.FieldTable.matrixTable, "Fields & Types in MetaData");

            // now read the same excel table without field definitions
            md = new MetaData();    // reset meta data
            xtbl2.ReadFromExcelTable(xlist, md);
            xtbl2.ViewTable("MatrixTableX read from excel table - after MetaData reset");
            System.Diagnostics.Debug.WriteLine("MetaData after reset & read table:\n " + md);

            // now read all tables from excel
            md = new MetaData();    // reset meta data
            string[] TableNames;
            var TableList = ExcelFunc_NO.GetAllExcelTables(wb, out TableNames);

            foreach (var tbl in TableList)
            {
                xtbl2.ReadFromExcelTable(tbl, md);
                xtbl2.ViewTable("MatrixTableX read from excel table - " + tbl.Name);
            }
            System.Diagnostics.Debug.WriteLine("MetaData after reading all tables in excel:\n " + md);
        }

        /// <summary>
        /// Test Read and Write methods in ExcelFunc:
        /// Read table from Excel range, Write table to Excel range
        /// </summary>
        internal void Test_ReadFrom_WriteTo_Range()
        {
            // demo excel file in Resources directory
            string filename = "CalcsExcelDemo.xlsm";
            string ProjectPath = Path.GetDirectoryName(Path.GetDirectoryName(System.IO.Directory.GetCurrentDirectory()));
            string xfilepath = ProjectPath + @"\Resources\" + filename;
            // string xfilepath = @"C:\Users\...\CalcsExcelIntegration\CalcsExcelIntegration1.01\Resources\CalcsExcelDemo.xlsm";

            // define metadata
            MetaData md = MetaData.CreateEmptyMetaData();
            MetaData.AddNewField(md, "country", FieldType.TextAttribute);
            MetaData.AddNewField(md, "category", FieldType.TextAttribute);
            MetaData.AddNewField(md, "brand", FieldType.TextAttribute);
            MetaData.AddNewField(md, "product", FieldType.TextAttribute);
            MetaData.AddNewField(md, "modelyear", FieldType.IntegerAttribute);
            MetaData.AddNewField(md, "typeid", FieldType.IntegerAttribute);
            MetaData.AddNewField(md, "date", FieldType.DateAttribute);
            MetaData.AddNewField(md, "costs", FieldType.KeyFigure);
            MetaData.AddNewField(md, "price", FieldType.KeyFigure);
            MetaData.AddNewField(md, "margin", FieldType.KeyFigure);
            MetaData.AddNewField(md, "sales", FieldType.KeyFigure);

            // create tables
            MatrixTable CostTable, MarginTable1, MarginTable2;
            Create_TestTables(md, out CostTable, out MarginTable1, out MarginTable2);
            CostTable = CostTable.PartitionRow(NumVector.CreateSequenceVector(0, 1, 100));

            var xtbl = new MatrixTableX(CostTable);

            // get current application instance
            // excel file CalcsExcelDemo must be opened
            Excel.Application xlapp = ExcelFunc_NO.GetExcelApplicationInstance();

            // check if workbook is open
            if (!ExcelFunc_NO.IsExcelWBOpen2(xlapp, xfilepath))
            {
                System.Diagnostics.Debug.WriteLine("Workbook is not opened!");
                return;
            }

            // get workbook object
            System.Diagnostics.Debug.WriteLine("Get workbook object, file: " + filename);
            Excel.Workbook wb = xlapp.Workbooks[filename];  // filepath causes errors

            // get worksheet object
            System.Diagnostics.Debug.WriteLine("Get worksheet object, named Sheet1");
            Excel.Worksheet wsheet = (Excel.Worksheet) wb.Worksheets["Sheet1"];

            // write MatrixTable to worksheet
            string TopLeftCellAddr = "C3";
            System.Diagnostics.Debug.WriteLine("Write MatrixTable to worksheet");
            ExcelFunc_NO.WriteTableToExcel(wsheet, CostTable, TopLeftCellAddr);

            // Read MatrixTable from range
            System.Diagnostics.Debug.WriteLine("Read MatrixTable from range");
            
            // Excel.Range range = wsheet.get_Range(Cell1: TopLeftCellAddr).get_Resize(CostTable.RowCount + 1, CostTable.ColumnCount);
            Excel.Range range = wsheet.get_Range(TopLeftCellAddr).get_Resize(CostTable.RowCount + 1, CostTable.ColumnCount);
            
            MatrixTable tbl = ExcelFunc_NO.ReadMatrixTableFromRange(range, md);
            MatrixTable.View_MatrixTable(tbl, "MatrixTable read from excel range");
            
            return;

            DataTable dt = CostTable.ToDataTable();
            // ExcelFunc.WriteTableToExcelSheet(ws, dt, "D2:G5");
        }

        // Test example ExcelTable.GetPriceTable
        internal static void Test_GetPriceTable()
        {
            // demo excel file in Resources directory
            string filename = "CalcsExcelDemo.xlsm";
            string ProjectPath = Path.GetDirectoryName(Path.GetDirectoryName(System.IO.Directory.GetCurrentDirectory()));
            string xfilepath = ProjectPath + @"\Resources\" + filename;

            // get current application instance
            // excel file CalcsExcelDemo must be opened
            Excel.Application xlapp = ExcelFunc_NO.GetExcelApplicationInstance();

            // check if workbook is open
            if (!ExcelFunc_NO.IsExcelWBOpen2(xlapp, xfilepath))
            {
                System.Diagnostics.Debug.WriteLine("Workbook is not opened!");
                return;
            }

            // get workbook object
            System.Diagnostics.Debug.WriteLine("Get workbook object, file: " + filename);
            Excel.Workbook wb = xlapp.Workbooks[filename];  // filepath causes errors

            // get excel table objects as inputs
            Excel.ListObject xMeta = ((Excel.Worksheet)wb.Worksheets["MetaData"])
                .ListObjects["Meta"];

            Excel.ListObject xCost = ((Excel.Worksheet)wb.Worksheets["CostTable"])
                .ListObjects["Cost"];

            Excel.ListObject xMargin = ((Excel.Worksheet)wb.Worksheets["MarginTable1"])
                .ListObjects["Margin1"];


            // get worksheet object
            System.Diagnostics.Debug.WriteLine("Get worksheet object, named Sheet1");
            Excel.Worksheet wsheet = (Excel.Worksheet) wb.Worksheets["Sheet1"];

            // call function
            var et = new ExcelTable();
            // et.GetPriceTable1(xCost, xMargin, xMeta, wsheet, "Price1");

            // call function
            et.GetPriceTable("Cost", "Margin1", "Meta", wb.FullName, "Price1");

            wsheet.Activate();

            // find ListObject in Workbook
            Excel.ListObject xlist = ExcelFunc_NO.GetListObject(wb, "Price1");

            if (xlist != null)
            {
                System.Diagnostics.Debug.WriteLine("ListObject Price1 is found "
                    + "in worksheet named " + ((Excel.Worksheet) xlist.Parent).Name);
            }
        }

        // Add a .net method to formula list in Excel
        internal static void AddMethodToExcelFormulaMenu()
        {
            // demo excel file in Resources directory
            string filename = "CalcsExcelDemo.xlsm";
            string ProjectPath = Path.GetDirectoryName(Path.GetDirectoryName(System.IO.Directory.GetCurrentDirectory()));
            string xfilepath = ProjectPath + @"\Resources\" + filename;

            // get current application instance
            // excel file CalcsExcelDemo must be opened
            Excel.Application xlapp = ExcelFunc_NO.GetExcelApplicationInstance();

            /*
            xlapp.MacroOptions2(Macro: "Xtable.CombineTables",
                Description: "Combine two input tables",
                Category: "Table Functions"); */

            xlapp.MacroOptions("Xtable.CombineTables", "Combine two input tables", "Table Functions"); 
        }

        // Test DNA method ExcelTableDNA.GetPriceTable2()
        internal static void TEST_GetPriceTable2_DNA()
        {
            // demo excel file in Resources directory
            string filename;
            filename = "FinaquantExcelDemo_2010.xlsm";
            // filename = "FinaquantExcelDemo_2003.xls";

            string ProjectPath = Path.GetDirectoryName(Path.GetDirectoryName(System.IO.Directory.GetCurrentDirectory()));
            string xfilepath = ProjectPath + @"\Resources\" + filename;

            System.Diagnostics.Debug.WriteLine("Excel file path: " + xfilepath);

            // get current application instance
            // excel file CalcsExcelDemo must be opened
            Excel.Application xlapp = ExcelFunc_NO.GetExcelApplicationInstance();

            // check if workbook is open
            if (!ExcelFunc_NO.IsExcelWBOpen2(xlapp, xfilepath))
            {
                System.Diagnostics.Debug.WriteLine("Workbook is not open!");
                return;
            }

            // get workbook object
            System.Diagnostics.Debug.WriteLine("Get workbook object, file: " + filename);
            Excel.Workbook wb = xlapp.Workbooks[filename];  // filepath causes errors

            // call method
            // ExcelTableDNA.GetPriceTable3(wbook: wb, xCostTblName: "Cost", xMarginTblName: "Margin1");
            ExcelTableDNA.GetPriceTable_macro();
        }

        // Test cell types
        internal static void TEST_CellTypes()
        {

        }

        // Test calling methods by name
        internal static void TEST_GeneralMethodCallers()
        {
            // define metadata
            MetaData md = MetaData.CreateEmptyMetaData();
            MetaData.AddNewField(md, "country", FieldType.TextAttribute);
            MetaData.AddNewField(md, "category", FieldType.TextAttribute);
            MetaData.AddNewField(md, "brand", FieldType.TextAttribute);
            MetaData.AddNewField(md, "product", FieldType.TextAttribute);
            MetaData.AddNewField(md, "modelyear", FieldType.IntegerAttribute);
            MetaData.AddNewField(md, "typeid", FieldType.IntegerAttribute);
            MetaData.AddNewField(md, "date", FieldType.DateAttribute);
            MetaData.AddNewField(md, "costs", FieldType.KeyFigure);
            MetaData.AddNewField(md, "price", FieldType.KeyFigure);
            MetaData.AddNewField(md, "margin", FieldType.KeyFigure);
            MetaData.AddNewField(md, "sales", FieldType.KeyFigure);

            // create tables
            MatrixTable CostTable, MarginTable1, MarginTable2;
            Create_TestTables(md, out CostTable, out MarginTable1, out MarginTable2);
            CostTable = CostTable.SelectRows(NumVector.CreateSequenceVector(0, 1, 100));

            // init MatrixTable
            var tblx = new MatrixTableX(CostTable);

            // call a method directly
            object result = tblx.matrixTable.GetFieldValue("product", 5);
            System.Diagnostics.Debug.WriteLine("Result of GetFieldValue(): " + result);

            // call the same method by its name
            result = tblx.CallMethodByName("GetFieldValue",
                new string[] { "System.String", "System.Int32" },
                new object[] { "product", 5});

            System.Diagnostics.Debug.WriteLine("Result of GetFieldValue() by name (1): " + result);

            // call the same method by its name
            result = ExcelFunc_NO.CallAnyMethodByName(typeof(MatrixTable), tblx.MatrixTableObj,
                "GetFieldValue",
                new Type[] { typeof(string), typeof(int) },
                new object[] { "product", 5 });

            System.Diagnostics.Debug.WriteLine("Result of GetFieldValue() by name (3): " + result);

            // CallNonStaticMethod with type names
            result = ExcelFunc_NO.CallAnyMethodByName("MatrixTable", tblx.MatrixTableObj,
                "GetFieldValue",
                new string[] { "System.String", "System.Int32" },
                new object[] { "product", 5 });

            System.Diagnostics.Debug.WriteLine("Result of GetFieldValue() by name (3): " + result);

            // see: Getting a System.Type from type's partial name
            // http://stackoverflow.com/questions/179102/getting-a-system-type-from-types-partial-name
            // Type.GetType Method (String)
            // http://msdn.microsoft.com/en-us/library/w3f99sx1%28v=vs.110%29.aspx

            System.Diagnostics.Debug.WriteLine("Namespace of MatrixTable: " + typeof(MatrixTable).Namespace);

            System.Diagnostics.Debug.WriteLine("Assembly of MatrixTable: " + typeof(MatrixTable).Assembly);

            System.Diagnostics.Debug.WriteLine("Qualified Assembly Name of MatrixTable: " 
                + typeof(MatrixTable).AssemblyQualifiedName);

            // Get type from class name
            System.Diagnostics.Debug.WriteLine(@"Type.GetType(""MatrixTable""): " 
                + Type.GetType("MatrixTable"));  // returns null

            System.Diagnostics.Debug.WriteLine(@"Type.GetType(""FinaquantCalcs.MatrixTable""): "
                + Type.GetType("FinaquantCalcs.MatrixTable"));  // returns null

            System.Diagnostics.Debug.WriteLine(@"Type.GetType(""int""): " + Type.GetType("System.String[]")); // returns null

            System.Diagnostics.Debug.WriteLine(@"typeof(int): " + typeof(int)); // returns System.Int32

            // Get type from class/type name, Helper method
            System.Diagnostics.Debug.WriteLine("Type of 'MatrixTable': "
                + ExcelFunc_NO.GetTypeFromTypeName("MatrixTable"));   // returns FinaquantCalcs.MatrixTable

            System.Diagnostics.Debug.WriteLine("Type of 'System.Double': "
                + ExcelFunc_NO.GetTypeFromTypeName("System.Double"));

            System.Diagnostics.Debug.WriteLine("Type of 'System.Int32': "
                + ExcelFunc_NO.GetTypeFromTypeName("System.Int32"));

            System.Diagnostics.Debug.WriteLine("Type of 'MatrixTableX': "
                + ExcelFunc_NO.GetTypeFromTypeName("MatrixTableX"));      // returns null

            System.Diagnostics.Debug.WriteLine("Type of 'MetaData': "
                + ExcelFunc_NO.GetTypeFromTypeName("MetaData"));

            // Test calling a static method of MatrixTable: MatrixTable.CombineTables
            // All parameters are needed; entering only mandatory parameters produces error

            result = tblx.CallMethodByName("CombineTables",
                new string[] { "MatrixTable", "MatrixTable", typeof(bool).FullName, 
                    typeof(string).FullName, typeof(int).FullName },
                new object[] { CostTable, MarginTable1, true, "ALL", 0 });  // works!

            ((MatrixTable)result).ViewTable("Combined Table");

            // same example with explicit type names
            result = tblx.CallMethodByName("CombineTables",
                new string[] { "MatrixTable", "MatrixTable", "System.Boolean", 
                    "System.String", "System.Int32" },
                new object[] { CostTable, MarginTable1, true, "ALL", 0 });  // works!

            ((MatrixTable)result).ViewTable("Combined Table - explicit type names");

            // same example with more general helper func
            ExcelFunc_NO.CallAnyMethodByName(ClassName: "MatrixTable", Instance: CostTable,
                MethodName: "CombineTables",
                ParameterTypeNames: new string[] { "MatrixTable", "MatrixTable", "System.Boolean", 
                    "System.String", "System.Int32" },
                parameters: new object[] { CostTable, MarginTable1, true, "ALL", 0 });

            ((MatrixTable)result).ViewTable("Combined Table - HelperFunc");

            // convert MatrixTable to MatrixTableX
            tblx = new MatrixTableX(result);
            tblx.ViewTable("MatrixTableX");
        }

        // test simple distribution
        internal static void TEST_Distribution()
        {
            // define metadata
            MetaData md = MetaData.CreateEmptyMetaData();
            MetaData.AddNewField(md, "country", FieldType.TextAttribute);
            MetaData.AddNewField(md, "category", FieldType.TextAttribute);
            MetaData.AddNewField(md, "brand", FieldType.TextAttribute);
            MetaData.AddNewField(md, "product", FieldType.TextAttribute);
            MetaData.AddNewField(md, "modelyear", FieldType.IntegerAttribute);
            MetaData.AddNewField(md, "typeid", FieldType.IntegerAttribute);
            MetaData.AddNewField(md, "date", FieldType.DateAttribute);
            MetaData.AddNewField(md, "costs", FieldType.KeyFigure);
            MetaData.AddNewField(md, "price", FieldType.KeyFigure);
            MetaData.AddNewField(md, "margin", FieldType.KeyFigure);
            MetaData.AddNewField(md, "sales", FieldType.KeyFigure);

            // create tables
            MatrixTable CostTable, MarginTable1, MarginTable2;
            Create_TestTables(md, out CostTable, out MarginTable1, out MarginTable2);
            // CostTable = CostTable.SelectRows(NumVector.CreateSequenceVector(0, 1, 100));


            // define table structure for DistrKeyTbl1
            var TeamTblFields = TableFields.CreateEmptyTableFields(md);
            TableFields.AddNewField(TeamTblFields, "department", FieldType.TextAttribute);
            TableFields.AddNewField(TeamTblFields, "team", FieldType.TextAttribute);

            MatrixTable TeamTbl = MatrixTable.CreateTableWithElements_A(TeamTblFields,
                "Management", "Management Team A",
                "Management", "Management Team B",
                "Management", "Markteting Team X",
                "Management", "Marketing Team Y",
                "Production", "Production Team 1",
                "Production", "Production Team 2");

            // get aggregated cost table
            MatrixTable Tbl = CostTable.SelectColumns(TextVector.CreateVectorWithElements(
                "category", "brand"));

            NumVector nv1, nv2;
            Tbl = MatrixTable.UniqueAttributeRows(Tbl, out nv1, out nv2);

            // cartesian multiplication of tables
            MatrixTable DistrKeyTbl1 = MatrixTable.CombinateTableRows(Tbl, TeamTbl);

            // insert key figure
            md.AddFieldIfNew("distr_ratio", FieldType.KeyFigure);
            DistrKeyTbl1 = MatrixTable.InsertNewColumn(DistrKeyTbl1, "distr_ratio", 0.0);
            DistrKeyTbl1 = MatrixTable.AssignRandomValues(DistrKeyTbl1, "distr_ratio", 0.1, 0.5);
            DistrKeyTbl1 = MatrixTable.Round(DistrKeyTbl1, 2);

            // view table 
            DistrKeyTbl1.ViewTable("Distribution Key Table");
            CostTable.ViewTable("Cost Table");

            // call distrib function
            MatrixTable ResultantTbl = AppUtility.SimpleDistribution(CostTable, DistrKeyTbl1,
                "costs_distrib", "sum_keys");

            ResultantTbl.ViewTable("Distributed Costs");
            Console.WriteLine("Row size of resultant table: " + ResultantTbl.RowCount);

            HelpCalcs.ProductInformation();
            
        }

        // Helper method for creating cost and margin tables
        private static void Create_TestTables(MetaData md, out MatrixTable CostTable,
            out MatrixTable MarginTable1, out MatrixTable MarginTable2)
        {
            // define metadata
            /*
            md = MetaData.CreateEmptyMetaData();
            MetaData.AddNewField(md, "country", FieldType.TextAttribute);
            MetaData.AddNewField(md, "category", FieldType.TextAttribute);
            MetaData.AddNewField(md, "brand", FieldType.TextAttribute);
            MetaData.AddNewField(md, "product", FieldType.TextAttribute);
            MetaData.AddNewField(md, "modelyear", FieldType.IntegerAttribute);
            MetaData.AddNewField(md, "typeid", FieldType.IntegerAttribute);
            MetaData.AddNewField(md, "date", FieldType.DateAttribute);
            MetaData.AddNewField(md, "costs", FieldType.KeyFigure);
            MetaData.AddNewField(md, "price", FieldType.KeyFigure);
            MetaData.AddNewField(md, "margin", FieldType.KeyFigure);
            MetaData.AddNewField(md, "sales", FieldType.KeyFigure);
             * */

            // define table structure
            var CostTableFields = TableFields.CreateEmptyTableFields(md);
            // TableFields.AddNewField(CostTableFields, "country");
            TableFields.AddNewField(CostTableFields, "category");
            TableFields.AddNewField(CostTableFields, "product");
            TableFields.AddNewField(CostTableFields, "brand");
            TableFields.AddNewField(CostTableFields, "modelyear");
            TableFields.AddNewField(CostTableFields, "date");
            TableFields.AddNewField(CostTableFields, "costs");

            // create test table by combinating field values
            // ... random values for key figures

            // attribute values
            // text attributes
            // TextVector CountryVal = TextVector.CreateVectorWithElements("Peru", "Paraguay", "Argentina", "Brasil");
            TextVector CategoryVal = TextVector.CreateVectorWithElements("Economy", "Luxury", "Sports", "Family");
            TextVector BrandVal = TextVector.CreateVectorWithElements("Toyota", "Honda", "BMW", "Audi");
            TextVector ProductVal = TextVector.CreateVectorWithElements("Car", "Bus", "Motor");
            // numeric attribûtes
            NumVector ModelVal = NumVector.CreateVectorWithElements(2008, 2009, 2010);
            NumVector DateVal = NumVector.CreateSequenceVector(
                StartValue: DateFunctions.DayToNumber(1, 1, 2010),
                Interval: 10, nLength: 5);

            // initiate field value dictionaries
            var TextAttribValues = new Dictionary<string, TextVector>();
            var NumAttribValues = new Dictionary<string, NumVector>();
            // assign a value vector to each field
            // TextAttribValues["country"] = CountryVal;
            TextAttribValues["category"] = CategoryVal;
            TextAttribValues["product"] = ProductVal;
            TextAttribValues["brand"] = BrandVal;
            NumAttribValues["modelyear"] = ModelVal;
            NumAttribValues["date"] = DateVal;

            // default range for all key figures
            KeyValueRange DefaultRangeForAllKeyFigures = KeyValueRange.CreateRange(5000, 10000);

            // range for selected key figures
            var RangeForSelectedKeywords = new Dictionary<string, KeyValueRange>();
            RangeForSelectedKeywords["margin"] = KeyValueRange.CreateRange(0.10, 0.60);

            //***************************************************************************
            // Create Cost Table
            //***************************************************************************

            CostTable = MatrixTable.CombinateFieldValues_B(CostTableFields,
                TextAttribValues, NumAttribValues, DefaultRangeForAllKeyFigures, RangeForSelectedKeywords);

            // round all key figures to 2 digits after decimal point
            CostTable = MatrixTable.TransformKeyFigures(CostTable, x => Math.Round(x, 2),
                InputKeyFig: null, OutputKeyFig: null);

            // view table
            // MatrixTable.View_MatrixTable(CostTable, "Cost table");

            //***************************************************************************
            // Create Margin Tables
            //***************************************************************************

            // define table structure for MarginTable1
            var MarginTable1Fields = TableFields.CreateEmptyTableFields(md);
            TableFields.AddNewField(MarginTable1Fields, "category");
            TableFields.AddNewField(MarginTable1Fields, "margin");

            // create MarginTable1 with elements
            MarginTable1 = MatrixTable.CreateTableWithElements_A(MarginTable1Fields,
                "Economy", 0.20,
                "Luxury", 0.50,
                "Sports", 0.35,
                "Family", 0.30
                );

            // define table structure for MarginTable2
            var MarginTable2Fields = TableFields.CreateEmptyTableFields(md);
            TableFields.AddNewField(MarginTable2Fields, "category");
            TableFields.AddNewField(MarginTable2Fields, "brand");
            TableFields.AddNewField(MarginTable2Fields, "margin");

            // create MarginTable1 with elements
            MarginTable2 = MatrixTable.CreateTableWithElements_A(MarginTable2Fields,
                "Economy", "ALL", 0.20,
                "Luxury", "Toyota", 0.30,
                "Luxury", "Honda", 0.35,
                "Luxury", "BMW", 0.40,
                "Luxury", "Audi", 0.45,
                "Sports", "ALL", 0.50,
                "Family", "ALL", 0.33
                );
        }
    }
}
